package com.dldata.devtools30.controller.si;

/**
 * Created by ai on 2017/1/11.
 */


import com.alibaba.fastjson.JSON;
import com.dldata.devtools30.businessmodel.SessionUserInfoEntity;
import com.dldata.devtools30.controller.DevBase30Controller;
import com.dldata.devtools30.entity.DevtApiLogEntity;
import com.dldata.devtools30.entity.DevtApiSubEntity;
import com.dldata.devtools30.entity.DevtApiTjoptEntity;
import com.dldata.devtools30.service.api.DevtApiMainEntityService;
import com.dldata.devtools30.service.api.DevtApiSubEntityService;
import com.dldata.devtools30.service.api.DevtApiTjoptEntityService;
import com.dldata.devtools30.utils.*;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.StatementCallback;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

import java.io.IOException;
import java.io.Serializable;
import java.math.BigDecimal;
import java.sql.*;
import java.util.Date;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

import static com.dldata.devtools30.utils.Util.DEVTOOLUSER;


/**

 * 将请求的commonApi与管理的Controller分开
 */
@Controller
@RequestMapping(value = "/ApiSearchService")
@Slf4j
public class ApiSearchServiceController extends DevBase30Controller {
    
    @Autowired
    private DevtApiTjoptEntityService devtApiTjoptEntityService;
    
    @Autowired
    private DevtApiSubEntityService devtApiSubEntityService;
    
    @Autowired
    private DevtApiMainEntityService apiMainEntityService;
    
    /**
     * token 校验后台请求
     * 1.校验参数并解析出功能对象和分页数据
     * 1.1.假如传pid就以pid为准,查询出DevtApiSubEntity,假如查询出来查询出DevtApiSubEntity,直接使用第一个,忽略mid和dm
     * 1.2.第一步没有查询出DevtApiSubEntity,使用mid和dm查询DevtApiSubEntity,假如查询出来查询出DevtApiSubEntity,直接使用第一个
     * 1.3.第二步没有查询出DevtApiSubEntity,使用dm查询DevtApiSubEntity,假如查询出来查询出DevtApiSubEntity,直接使用第一个
     *
     * 2.逐个执行SQL并获取结果和总页数
     *
     * 3.依据结果组装返回JSON
     *
     * 4.记录接口日志
     *
     * @param request
     * @param res
     * @return
     */
    @RequestMapping(value = "")
    @ResponseBody
    public Object apiexec(
            HttpServletRequest request, HttpServletResponse res,@RequestParam Map<String, Object> paramMap) throws IOException {
        
        DevtApiSubEntity devtApiSubEntity=getDevtApiSubEntity(request.getParameter("p_id") ,request.getParameter("p_dm") );
        if(devtApiSubEntity==null){
            HashMap resultMap= new HashMap();
            resultMap.put("success",Boolean.FALSE);
            resultMap.put("msg","未查询到API:"+request.getParameter("p_dm"));
            return resultMap;
        }
        
        Map<String, Object> jsonMap=new HashMap<>(paramMap);
        
        Object execSql = execSql(request, res, devtApiSubEntity, jsonMap);
        return execSql;
    }

    protected Object execSql(HttpServletRequest request, HttpServletResponse res, DevtApiSubEntity devtApiSubEntity, Map<String, Object> jsonMap) {
        
        List<DevtApiTjoptEntity> optlist =devtApiTjoptEntityService.getAllDataByIdOrDm(devtApiSubEntity.getId());
        
        List validateResult = new LinkedList<>();
        if(DevtApiSubEntityService.sqltype.replaceconcat.name().equalsIgnoreCase(devtApiSubEntity.getSqltype())){
            final Pattern sqlPattern = Pattern.compile(Public.sqlZhuru, Pattern.CASE_INSENSITIVE);
            optlist.forEach(
                    apiTjoptEntity -> {
                        String argValueOrg = StringUtil.isNullOrSpace(request.getParameter(apiTjoptEntity.getArgname())) ? "" : StringUtil.unescape(request.getParameter(apiTjoptEntity.getArgname()));
                        String argsPost = StringUtil.isEmptyOrLength0(jsonMap.get(apiTjoptEntity.getArgname())) ? "" : jsonMap.get(apiTjoptEntity.getArgname()).toString();
                        argValueOrg=StringUtil.isEmptyOrLength0(argValueOrg)? argsPost :argValueOrg;
                        if(sqlPattern.matcher(argValueOrg).find()){
                            HashMap hashMap = new HashMap();
                            hashMap.put("errorinfo","参数非法");
                            hashMap.put("fieldName",apiTjoptEntity.getArgname());
                            validateResult.add(hashMap);
                        }
                    }
            );
        }
        
        if(validateResult.size()>0){
            HashMap resultMap= new HashMap();
            resultMap.put("success",validateResult.size()==0);
            resultMap.put("resultdata",validateResult);
            resultMap.put("msg",validateResult);
            return resultMap;
        }
        
        //获取替换后的执行sql 最终sql 页码,页容量
        SqlAndParameters sqlAndParameterses = getExcuteSql(request, devtApiSubEntity,optlist,jsonMap);
        switch(DevtApiMainEntityService.FunctionType.valueOf(devtApiSubEntity.getLx())){
            case DATALIST:{
                try {
                    Object resultJson = execSelectFunction(devtApiSubEntity, sqlAndParameterses);
                    String resultJson1 =resultJson instanceof String? resultJson.toString():JSON.toJSONString(resultJson.toString());
                    saveExecLog(devtApiSubEntity,jsonMap, sqlAndParameterses, resultJson1,"0","");
                    return resultJson;
                } catch (Exception ex) {
                    log.error(ex.getMessage(),ex);
                    saveExecLog(devtApiSubEntity,jsonMap, sqlAndParameterses, ex.getMessage(), "1",ex.getMessage());
                    HashMap resultMap= new HashMap();
                    resultMap.put("success",false);
                    resultMap.put("msg","系统错误，请稍候再试::"+ex.getMessage());
                    return resultMap;
                }
            }
            default:{
                HashMap resultMap= new HashMap();
                resultMap.put("success",false);
                resultMap.put("msg","API类型有误");
                return resultMap;
            }
        }
    }
    
    //创建存储过程
    private String getProSql(String sql){
      List<Map<String,Object>> proList=  this.jdbcTemplate.queryForList(" SELECT 1 FROM sysobjects WHERE name='Pro_dev_RunSql' AND type='P'");
        if(proList.size()<=0){
            String prosql="create PROCEDURE Pro_dev_RunSql\n" +
                    "\t\t@sql VarChar(max)\n" +
                    "\tAS\n" +
                    "\tBEGIN\n" +
                    "\t\tSET NOCOUNT ON;\n" +
                    "\t\t exec (@sql)\n" +
                    "\tEND";
            this.jdbcTemplate.update(prosql);
        }
        sql=" exec Pro_dev_RunSql '"+sql.replaceAll("'","''")+"' ";
        return sql;
    }

    private Object execSelectFunction(DevtApiSubEntity pFunctionsubEntity, SqlAndParameters sqlAndParametersOne) {
        List<Object> returnData = new LinkedList<>();
        List<Integer> returnDataTotalCount = new LinkedList<Integer>();
        //存储过程模式
        if(DevtApiSubEntityService.sqltype.replaceconcat.name().equals(pFunctionsubEntity.getSqltype())
                &&1==pFunctionsubEntity.getIsprocedure()
                ){
            String sql=getProSql(sqlAndParametersOne.sql);
            LinkedList<Map<String, Object>> dataListResult =  new LinkedList(this.jdbcTemplate.queryForList(sql));

            returnData.add(dataListResult);
            returnDataTotalCount.add(dataListResult.size());
        }else{
            //查询记录总数
            Serializable totalCount=0;
            String func_Sql_TotalTmp = sqlAndParametersOne.sql.toLowerCase().replaceAll("\\s"," ").
                    replaceFirst("select.*?\\s+from\\s+", "select count(1) as total from ");
            int endIndex = func_Sql_TotalTmp.toString().toLowerCase().lastIndexOf(" order by ");
            func_Sql_TotalTmp=endIndex>0?func_Sql_TotalTmp.substring(0, endIndex):func_Sql_TotalTmp;
            List<Map<String, Object>> dataList=new LinkedList<>();
            if(DevtApiSubEntityService.sqltype.nameprecompile.name().equals(pFunctionsubEntity.getSqltype())){
                MapSqlParameterSource parameters = new MapSqlParameterSource();
                for (int j = 0; j < sqlAndParametersOne.parameters.length; j++) {
                    parameters.addValue( sqlAndParametersOne.parametersNames[j], sqlAndParametersOne.parameters[j]);
                }
                dataList = nbjdbcTemplate.queryForList(func_Sql_TotalTmp.toString(), parameters);
            }else{
                dataList = jdbcTemplate.queryForList(func_Sql_TotalTmp.toString(), sqlAndParametersOne.parameters);
            }
            totalCount=dataList.size()==0?0:Integer.parseInt(dataList.get(0).get("total").toString());
    
            //查询数据
            LinkedList goableResponseJson =getOneDataFunction(sqlAndParametersOne, pFunctionsubEntity);
            returnData.add(goableResponseJson);
            returnDataTotalCount.add(Integer.parseInt(totalCount.toString()));
        }
        
        Map<String,Object> dataMap = new HashMap<>();
        dataMap.put("success",true);
        dataMap.put("msg","处理成功");
        for (int i = 0; i <returnData.size() ; i++) {
            
            Integer returnTotalCount = returnDataTotalCount.get(i);
            //不传分页直接使用List.size
            Object valueList = returnData.get(i);
            if(valueList instanceof List&&returnTotalCount==0){
                returnTotalCount=returnTotalCount>((List)valueList).size()?returnTotalCount:((List)valueList).size();
            }
        
            int fenyeTotal= returnTotalCount /sqlAndParametersOne.pageSize;
            fenyeTotal= returnTotalCount %sqlAndParametersOne.pageSize==0?fenyeTotal:(fenyeTotal+1);
            
            if(i==0){
                dataMap.put("Table", valueList);
                dataMap.put("pagecount",fenyeTotal);
                dataMap.put("totalcount",returnTotalCount);
            }else{
                dataMap.put("Table"+i, valueList);
                dataMap.put("pagecount"+i,fenyeTotal);
                dataMap.put("totalcount"+i,returnTotalCount);
            }
        }
        return dataMap;
    }

    /**
     * 记录日志 后面可以考虑写文件 已完成
     * @param pFunctionsubEntity
     * @param func_SqlAry
     * @param resultJson
     * @param status
     */
    private void saveExecLog(DevtApiSubEntity pFunctionsubEntity,Map<String, Object> jsonMap, SqlAndParameters func_SqlAry, String resultJson,String status,String errorinfo) {

        try{
            DevtApiLogEntity devtApiLogEntity=new DevtApiLogEntity();
            devtApiLogEntity.setCdate(new Timestamp(System.currentTimeMillis()));
            devtApiLogEntity.setEstatus(status);
            devtApiLogEntity.setSubid(pFunctionsubEntity.getId());
            devtApiLogEntity.setJsondata(JSON.toJSONString(jsonMap));
            devtApiLogEntity.setStrsql(func_SqlAry.sql);
            devtApiLogEntity.setErrorinfo(errorinfo);
            devtApiLogEntity.setSubdm(pFunctionsubEntity.getDm());
            devtApiLogEntity.setSubname(pFunctionsubEntity.getMc());
            commonApiDao.save(devtApiLogEntity);
        }catch (Exception e){
            log.trace(e.getMessage(),e);
        }
    }

    private LinkedList getOneDataFunction(SqlAndParameters sqlAndParametersOne, DevtApiSubEntity pFunctionsubEntity) {
        String searchSql=sqlAndParametersOne.sql;
        Object[] parameters=sqlAndParametersOne.parameters;
        String[] parameterNames=sqlAndParametersOne.parametersNames;
        int pageSize =sqlAndParametersOne.pageSize;
        int pageIndex =sqlAndParametersOne.pageIndex;
        
        //解析分页数据
        //页码从1开始,所以每一页的起始数应该是:(页码-1)*每页个数-1
        int start=(pageIndex-1)*pageSize;
        start=start<0?0:start;
        DBTypeUtil.DBTYPE dbtype=DBTypeUtil.getDbtype(jdbcTemplate.getDataSource());
        int skipNum=0;
        if(DBTypeUtil.DBTYPE.mysql.equals(dbtype)){
            String func_SqlTmp=searchSql+ " limit "+start+" , "+pageSize;
            searchSql=searchSql.trim().toLowerCase().indexOf("select")==0?func_SqlTmp.toString():searchSql;
        }else if(DBTypeUtil.DBTYPE.oracle.equals(dbtype)){
            StringBuilder tmpStringBuilder= new StringBuilder();
            tmpStringBuilder.append("select * from ");
            tmpStringBuilder.append(" ( ");
            tmpStringBuilder.append(searchSql.replace("select ", "select rownum as rownum_id,"));
            tmpStringBuilder.append(" ) ");
            tmpStringBuilder.append(" where rownum_id between "+start+" and "+(start+pageSize));
            searchSql=searchSql.trim().toLowerCase().indexOf("select")==0?tmpStringBuilder.toString():searchSql;
        }else if(DBTypeUtil.DBTYPE.sqlserver.equals(dbtype)){

            String tmpStr=searchSql.trim().startsWith("select")?"select":"";
            tmpStr=searchSql.trim().startsWith("SELECT")?"SELECT":tmpStr;

            if(tmpStr.length()>0){
                String toLowerCase = searchSql.replaceFirst(tmpStr, "").trim().toLowerCase();
                boolean hasDistinct=false;
                if(toLowerCase.startsWith("distinct")){
                    toLowerCase=toLowerCase.replaceFirst("distinct","");
                    hasDistinct=true;
                }
                String searchSql1Tmp="select top 0 "+toLowerCase;
                List<String> strFieldsList=new LinkedList<>();
                if(DevtApiSubEntityService.sqltype.nameprecompile.name().equals(pFunctionsubEntity.getSqltype())){
                    MapSqlParameterSource parametersMapSql = new MapSqlParameterSource();
                    for (int j = 0; j < parameters.length; j++) {
                        parametersMapSql.addValue(parameterNames[j],parameters[j]);
                    }
                    nbjdbcTemplate.query(searchSql1Tmp,parametersMapSql, new ResultSetExtractor<Object>() {
                        @Override
                        public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
                            ResultSetMetaData resultSetMetaData=rs.getMetaData();
                            for (int i=0;i<resultSetMetaData.getColumnCount();i++){
                                resultSetMetaData.getColumnLabel(i+1);
                                resultSetMetaData.getColumnName(i+1).toLowerCase();
                                strFieldsList.add(resultSetMetaData.getColumnName(i+1).toLowerCase());
                            }
                            return null;
                        }
                    });
                }else{
                    jdbcTemplate.query(searchSql1Tmp,parameters, new ResultSetExtractor<Object>() {
                        @Override
                        public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
                            ResultSetMetaData resultSetMetaData=rs.getMetaData();
                            for (int i=0;i<resultSetMetaData.getColumnCount();i++){
                                resultSetMetaData.getColumnLabel(i+1);
                                resultSetMetaData.getColumnName(i+1).toLowerCase();
                                strFieldsList.add(resultSetMetaData.getColumnName(i+1).toLowerCase());
                            }
                            return null;
                        }
                    });
                }

                //处理order by
                String searchSqlTmp = searchSql.toLowerCase().replaceAll("[\\s]?order[\\s]?by[\\s]?", " order by ");
                int lastIndexOfOrderby = searchSqlTmp.lastIndexOf(" order by ");
                //处理rownumberOrderbyField
                String rownumberOrderbyField ="";
                if(lastIndexOfOrderby>-1){
                    rownumberOrderbyField=searchSqlTmp.substring(lastIndexOfOrderby).toLowerCase();
                    rownumberOrderbyField=rownumberOrderbyField.trim().replaceFirst("order by ","");
                }else{
                    rownumberOrderbyField=strFieldsList.size()>2?strFieldsList.get(1):strFieldsList.get(0);
                }
                StringBuilder tmpStringBuilder= new StringBuilder();
                tmpStringBuilder.append("select row_number() over (order by ");
                tmpStringBuilder.append(rownumberOrderbyField);
                tmpStringBuilder.append(" ) rownumber , ");

                //处理子查询语句
                if(lastIndexOfOrderby >-1){
                    searchSql=searchSql.substring(0,lastIndexOfOrderby);
                }
                if(hasDistinct){
                    searchSql=searchSql.replaceFirst("distinct","");
                }
                searchSql = searchSql.replaceFirst(tmpStr, tmpStringBuilder.toString());

                //处理最终查询语句
                StringBuilder tmpStringBuilder1 = new StringBuilder();
                tmpStringBuilder1.append(" select ");
                if(hasDistinct){
                    tmpStringBuilder1.append(" distinct ");
                }
                tmpStringBuilder1.append(" *  from (");
                tmpStringBuilder1.append(searchSql);
                tmpStringBuilder1.append(") tableabc where rownumber between ");
                tmpStringBuilder1.append(start+1);
                tmpStringBuilder1.append(" and ");
                tmpStringBuilder1.append((start+pageSize));
                searchSql = tmpStringBuilder1.toString();
            }

        }else {
            //TODO:其它数据库
        }

        final int skipNumFinal=skipNum;
        final String func_SqlFinal=searchSql;
        log.info("searchSql: "+func_SqlFinal);
        log.info("searchSqlArgs: "+StringUtil.arrayToDelimitedString(parameters,","));

        if(DevtApiSubEntityService.sqltype.nameprecompile.name().equals(pFunctionsubEntity.getSqltype())){
            MapSqlParameterSource parametersMapSql = new MapSqlParameterSource();
            for (int j = 0; j < parameters.length; j++) {
                parametersMapSql.addValue(parameterNames[j],parameters[j]);
            }
           return nbjdbcTemplate.execute(func_SqlFinal, parametersMapSql, new PreparedStatementCallback<LinkedList<Map<String, Object>>>() {
                @Override
                public LinkedList<Map<String, Object>> doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
                    ps.execute();
                    return paraseOneStatementResult(ps, parameters, skipNumFinal, pFunctionsubEntity);
                }
            });
        }

        LinkedList<Map<String, Object>> dataListResult = parameters.length==0?
          jdbcTemplate.execute(new StatementCallback<LinkedList<Map<String, Object>>>() {
            @Override
            public LinkedList<Map<String, Object>> doInStatement(Statement stmt) throws SQLException, DataAccessException {
                stmt.execute(func_SqlFinal);
                return  paraseOneStatementResult(stmt, parameters, skipNumFinal, pFunctionsubEntity);
            }
        }):jdbcTemplate.execute(func_SqlFinal, new PreparedStatementCallback<LinkedList<Map<String, Object>>>() {
            @Override
            public LinkedList<Map<String, Object>> doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
                for (int i = 0; i < parameters.length; i++) {
                    ps.setObject(i + 1, parameters[i]);
                }
                ps.execute();
                return  paraseOneStatementResult(ps, parameters, skipNumFinal, pFunctionsubEntity);
            }
        });
        log.debug(" sql end.");
        return dataListResult;
    }

    private LinkedList<Map<String, Object>> paraseOneStatementResult(Statement statement, Object[] parameters, int skipNumFinal, DevtApiSubEntity pFunctionsubEntity) throws SQLException {
        //使用最后一个数据集
        LinkedList<Map<String, Object>> dataListList = new LinkedList<>();
        do {
            dataListList = new LinkedList();
            ResultSet sqlRowSet = statement.getResultSet();
            if(sqlRowSet==null){
                continue;
            }

            int tmpSkipCount = 0;
            ResultSetMetaData metaData = sqlRowSet.getMetaData();
            while (sqlRowSet.next()) {
                tmpSkipCount++;
                if (tmpSkipCount <= skipNumFinal) {
                    continue;
                }
                Map<String,Object> oneData = new LinkedHashMap<>();
                for (int i = 1; i <= metaData.getColumnCount(); i++) {
                    String columnName = metaData.getColumnLabel(i);
                    switch (metaData.getColumnType(i)){
                        case Types.DATE:{
                            java.sql.Date anInt = sqlRowSet.getDate(i);
                            String dateString =anInt==null?"": DateUtil.formatFromDate(DateUtil.FORMATER_YYYY_MM_DD_HH_MM_SS, anInt);
                            oneData.put(columnName, dateString);
                            break;
                        }
                        case Types.TIME:{
                            Time time =  sqlRowSet.getTime(i);
                            String dateString =time==null?"": DateUtil.formatFromDate(DateUtil.FORMATER_YYYY_MM_DD_HH_MM_SS, new Date(time.getTime()));
                            oneData.put(columnName, dateString);
                            break;
                        }
                        case Types.TIMESTAMP:{
                            Object timestamp1 = sqlRowSet.getObject(i);
                            Timestamp timestamp=new Timestamp(0);
                            if(timestamp1 instanceof oracle.sql.TIMESTAMP ){
                                oracle.sql.TIMESTAMP timestampTmp = (oracle.sql.TIMESTAMP)timestamp1;
                                try {
                                    timestamp=timestampTmp.timestampValue();
                                } catch (SQLException e) {
                                    e.printStackTrace();
                                }
                            }else if(timestamp1 instanceof oracle.sql.DATE ){
                                oracle.sql.DATE timestampTmp = (oracle.sql.DATE)timestamp1;
                                try {
                                    timestamp=timestampTmp.timestampValue();
                                } catch (Exception e) {
                                    e.printStackTrace();
                                }
                            }else if(timestamp1 instanceof oracle.sql.TIMESTAMPTZ ){
                                oracle.sql.TIMESTAMPTZ timestampTmp = (oracle.sql.TIMESTAMPTZ)timestamp1;
                                try {
                                    timestamp=timestampTmp.timestampValue();
                                } catch (Exception e) {
                                    e.printStackTrace();
                                }
                            }else{
                                timestamp = sqlRowSet.getTimestamp(i);
                            }
                            String dateString =timestamp==null?"": DateUtil.formatFromDate(DateUtil.FORMATER_YYYY_MM_DD_HH_MM_SS, new Date(timestamp.getTime()));
                            oneData.put(columnName, dateString);
                            break;
                        }
                        case Types.TINYINT:
                        case Types.SMALLINT:
                        case Types.INTEGER:{;
                            long anInt = sqlRowSet.getLong(i);
//                            oneData.put(columnName,String.valueOf(anInt));
                            BigDecimal bigDecimal=new BigDecimal(anInt);
                            oneData.put(columnName,bigDecimal);
                            break;
                        }
                        case Types.FLOAT:{;
                            float anInt = sqlRowSet.getFloat(i);
//                            oneData.put(columnName,String.valueOf(anInt));
                            BigDecimal bigDecimal=new BigDecimal(anInt);
                            oneData.put(columnName,bigDecimal);
                            break;
                        }
                        case Types.NUMERIC:
                        case Types.DECIMAL:
                        case Types.DOUBLE:{;
                            double anInt = sqlRowSet.getDouble(i);
//                            oneData.put(columnName,String.valueOf(anInt));
                            BigDecimal bigDecimal=new BigDecimal(anInt);
                            oneData.put(columnName,bigDecimal);
                            break;
                        }
                        case Types.CLOB:{;
                            try {
                                Clob colb= (Clob)sqlRowSet.getObject(i);
                                oneData.put(columnName,colb==null?"":colb.getSubString(1,(int)colb.length()));
                            } catch (Exception e) {
                                e.printStackTrace();
                            }
                            break;
                        }
                        case Types.BLOB:{;
                            try {
                                Blob blob= (Blob)sqlRowSet.getObject(i);
//                            oneData.put(columnName,new String(blob.getBytes(0, (int) blob.length()),"UTF-8"));
                                //@TODO 后面考虑转二进制可显示串
                                oneData.put(columnName,blob==null?"":blob.toString());
                            } catch (Exception e) {
                                e.printStackTrace();
                            }
                            break;
                        }
                        default:{
                            String anInt = sqlRowSet.getString(i);
                            anInt=anInt==null?"":anInt;
                            oneData.put(columnName, anInt);
                            break;
                        }
                    }
                }
                
                if(pFunctionsubEntity.getDatalx()==DevtApiMainEntityService.FunctionResultType.jsonmixcase.getValue()){
                    List<String> keyList=new LinkedList(oneData.keySet());
                    keyList.stream().forEach(
                            s -> {
                                oneData.put(s.toUpperCase(),oneData.get(s));
                                oneData.put(s.toLowerCase(),oneData.get(s));
                            }
                    );
                }
                dataListList.add(oneData);
            }
        } while (statement.getMoreResults() || statement.getUpdateCount() != -1);

        return dataListList;
    }

    /**
     *<!--替换拼接就是tjsql里面直接替换#values# sql里面#name#替换tjsql -->
     *<!--预编译就是tjsql里面直接替换#values#后作为预编译SQL参数 sql里面#name#替换? sql配置为and name like #name#  tjsql则为 '%#values#%'-->
     * @param request
     * @param pFunctionsubEntity
     * @param optlist
     * @param jsonMap
     * @return
     */
    private SqlAndParameters getExcuteSql(HttpServletRequest request, DevtApiSubEntity pFunctionsubEntity,List<DevtApiTjoptEntity> optlist,Map<String, Object> jsonMap) {
        int pageIndex=jsonMap.get("pageIndex")!=null? Integer.parseInt(jsonMap.get("pageIndex").toString()):1;
        int pageSize=jsonMap.get("pageSize")!=null? Integer.parseInt(jsonMap.get("pageSize").toString()):3000;
        
        String value = pFunctionsubEntity.getSql();
        if(DevtApiSubEntityService.sqltype.nameprecompile.name().equals(pFunctionsubEntity.getSqltype())){
            SqlAndParameters sqlAndParameters = parseOneSqlNamesPreCompile(request, value, optlist, jsonMap);
            sqlAndParameters.pageIndex=pageIndex;
            sqlAndParameters.pageSize=pageSize;
            return sqlAndParameters;
        }else if(DevtApiSubEntityService.sqltype.precompile.name().equals(pFunctionsubEntity.getSqltype())){
            SqlAndParameters sqlAndParameters = parseOneSqlPreCompile(request, value, optlist, jsonMap);
            sqlAndParameters.pageIndex=pageIndex;
            sqlAndParameters.pageSize=pageSize;
            return sqlAndParameters;
        }else{
            SqlAndParameters sqlAndParameters = parseOneSql(request, value, optlist, jsonMap);
            sqlAndParameters.pageIndex=pageIndex;
            sqlAndParameters.pageSize=pageSize;
            return sqlAndParameters;
        }
    }

    private SqlAndParameters parseOneSql(HttpServletRequest request,String sql, List<DevtApiTjoptEntity> optlist, Map<String, Object> jsonMap) {

        SqlAndParameters sqlAndParameters = new SqlAndParameters();
        List<DevtApiTjoptEntity> whereList =optlist.stream().filter(
                t -> t.getLx() == 1
        ).collect(Collectors.toList());
        List<DevtApiTjoptEntity> replaceList =optlist.stream().filter(
                t -> t.getLx() == 0
        ).collect(Collectors.toList());
        String whereStr = "";
        //先拼接WhereSql
        for (DevtApiTjoptEntity firstLevel : whereList) {
            String argsPost = StringUtil.isEmptyOrLength0(jsonMap.get(firstLevel.getArgname())) ? "" : jsonMap.get(firstLevel.getArgname()).toString();
            whereStr = whereStr + " " + firstLevel.getTjsql().replace("#values#", argsPost);
        }
        if (!StringUtil.isNullOrSpace(whereStr)) {
            sql = sql  + " where " + whereStr;
        }
        //拼接替换sql
        for (DevtApiTjoptEntity firstLevel : replaceList) {
            String argValueOrg = request.getParameter(firstLevel.getArgname());
            String argsPost = jsonMap.get(firstLevel.getArgname())==null ? null : jsonMap.get(firstLevel.getArgname()).toString();
            argValueOrg=argValueOrg==null? argsPost :argValueOrg;
            if (argValueOrg == null) {
                sql = sql.replace("#" + firstLevel.getArgname() + "#", " ");
            } else {
                String argValue = StringUtil.isEmptyOrLength0(argValueOrg) ?"":StringUtil.unescape(argValueOrg);
                argValue = Util.StrFormat(argValue);
                if (firstLevel.getTjsql().contains("$\\'")) {
                    argValue = argValue.replace("'", "''");
                }
                sql = sql.replace("#" + firstLevel.getArgname() + "#", firstLevel.getTjsql().replace("$\\'", "").replace("#values#", argValue));
            }
        }
        sqlAndParameters.sql=Util.replaceDefault(sql,request);
        return sqlAndParameters;
    }

    private SqlAndParameters parseOneSqlNamesPreCompile(HttpServletRequest request,String sql, List<DevtApiTjoptEntity> optlist, Map<String, Object> jsonMap) {
        SqlAndParameters sqlAndParameters = new SqlAndParameters();
        
        LinkedList<Object> valuelinkedList= new LinkedList();
        LinkedList<String> namelinkedList= new LinkedList();
        for (DevtApiTjoptEntity firstLevel : optlist) {
            String argValueOrg = request.getParameter(firstLevel.getArgname());
            String argsPost = StringUtil.isEmptyOrLength0(jsonMap.get(firstLevel.getArgname())) ? "" : jsonMap.get(firstLevel.getArgname()).toString();
            argValueOrg=StringUtil.isEmptyOrLength0(argValueOrg)? argsPost :argValueOrg;
            argValueOrg = StringUtil.isNullOrSpace(argValueOrg) ? "" : StringUtil.unescape(argValueOrg);
            argValueOrg =Util.StrFormat(argValueOrg);
            if (jsonMap.containsKey(firstLevel.getArgname())&&request.getParameter(firstLevel.getArgname())!=null) {
                if (firstLevel.getTjsql().contains("$\\'")) {
                    argValueOrg = argValueOrg.replace("'", "''");
                }
                sql = sql.replace("#" + firstLevel.getArgname() + "#", firstLevel.getTjsql());
                if("Array".equalsIgnoreCase(firstLevel.getCslx())){
                    valuelinkedList.add(Arrays.asList(argValueOrg.split(",")));
                }else{
                    valuelinkedList.add(argValueOrg);
                }
            }else{
                sql = sql.replace("#" + firstLevel.getArgname() + "#"," ");
                valuelinkedList.add("Number".equalsIgnoreCase(firstLevel.getCslx())?null:"");
            }
            namelinkedList.add(firstLevel.getArgname());
        }

        SessionUserInfoEntity sysStaffEntity = RightUtil.getCurrentUser(request);

        namelinkedList.add(DEVTOOLUSER);
        valuelinkedList.add(StringUtil.isEmptyOrLength0(sysStaffEntity.getDevtooluser())?"":sysStaffEntity.getDevtooluser());

        sqlAndParameters.sql=sql;
        sqlAndParameters.parameters=valuelinkedList.toArray();
        sqlAndParameters.parametersNames=namelinkedList.toArray(new String[namelinkedList.size()]);
        return sqlAndParameters;
    }

    private SqlAndParameters parseOneSqlPreCompile(HttpServletRequest request,String sql, List<DevtApiTjoptEntity> optlist, Map<String, Object> jsonMap) {
        SqlAndParameters sqlAndParameters = new SqlAndParameters();

        StringBuilder patternStr=new StringBuilder();
        HashMap<String,DevtApiTjoptEntity> functiontjoptEntityHashMap = new HashMap();
        for (DevtApiTjoptEntity firstLevel : optlist) {
            functiontjoptEntityHashMap.put(firstLevel.getArgname(),firstLevel);
            patternStr.append("#");
            patternStr.append(firstLevel.getArgname());
            patternStr.append("#|");
        }
        patternStr.append(DEVTOOLUSER);
        patternStr.append("|");

        LinkedList<Object> linkedList= new LinkedList();
        Pattern pattern = Pattern.compile(patternStr.toString());
        Matcher matcher=pattern.matcher(sql);
        while(matcher.find()){
            String argnameCofig = matcher.group();
            sql=sql.replaceFirst(argnameCofig, "?");
            matcher=pattern.matcher(sql);

            String argname = argnameCofig.substring(0, argnameCofig.length() - 1).substring(1);
            if(DEVTOOLUSER.equals(argnameCofig)){
                SessionUserInfoEntity sysStaffEntity = RightUtil.getCurrentUser(request);
                linkedList.add( StringUtil.isEmptyOrLength0(sysStaffEntity.getDevtooluser())?"":sysStaffEntity.getDevtooluser());
            }else{
                String argValueOrg = request.getParameter(argname);
                String argsPost = StringUtil.isEmptyOrLength0(jsonMap.get(argname)) ? "" : jsonMap.get(argname).toString();
                argValueOrg=StringUtil.isEmptyOrLength0(argValueOrg)? argsPost :argValueOrg;
                argValueOrg = StringUtil.isNullOrSpace(argValueOrg) ? "" : StringUtil.unescape(argValueOrg);
                argValueOrg =Util.StrFormat(argValueOrg);
                DevtApiTjoptEntity pFunctiontjoptEntity=functiontjoptEntityHashMap.get(argname);
                if (argValueOrg != null) {
                    if (pFunctiontjoptEntity.getTjsql().contains("$\\'")) {
                        argValueOrg = argValueOrg.replace("'", "''");
                    }
                    argValueOrg = pFunctiontjoptEntity.getTjsql().replace("$\\'", "").replace("#values#", argValueOrg);
                    linkedList.add(argValueOrg);
                }else{
                    linkedList.add("Number".equalsIgnoreCase(pFunctiontjoptEntity.getCslx())?null:"");
                }
            }
        }
        sqlAndParameters.sql=sql;
        sqlAndParameters.parameters=linkedList.toArray();
        return sqlAndParameters;
    }

    /**
     * 依据请求参数获取子功能
     * @param p_id
     * @param m_id
     * @param p_dm
     * @return
     */
    public DevtApiSubEntity getDevtApiSubEntity( String p_id,String p_dm){

            if(!StringUtil.isNullOrSpace(p_id)){
                
                List<DevtApiSubEntity> func_Model=devtApiSubEntityService.getOneData(p_id);
                return func_Model.size()==0?null:func_Model.get(0);
            }

            if(!StringUtil.isNullOrSpace(p_dm)){
                
                List<DevtApiSubEntity> func_Model = devtApiSubEntityService.getOneDataByDm(p_dm);
                return func_Model.size()==0?null:func_Model.get(0);
            }

            return null;
    }
    
    public static class SqlAndParameters{
        public String sql="";
        public Object[] parameters =new Object[0];
        public String[] parametersNames = new String[0];
        public int pageIndex=1;
        public int pageSize=3000;
    }
}

